package com.edu.hre.db.sql.admin;

import java.io.Serializable;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import com.edu.hre.common.utils.CommonUtils;
import com.edu.hre.db.pojo.HreEmployee;
import com.edu.hre.db.pojo.HreUser;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;
import com.edu.hre.struts.form.admin.UserForm;
import com.edu.hre.struts.form.admin.UserListForm;

public class UserDAOService implements Serializable {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = 3394301416150421253L;
	public static final String BEAN_NAME = "userDAOService";
	private BaseService baseService  = null;
	 
	public void setBaseService(BaseService baseService) {
		this.baseService = baseService;
	} 

	public List<UserForm> getListUserForm(UserListForm form, int limit, int offset){
		
		List<UserForm> listResult = new ArrayList<UserForm>();
		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();

		sql = "SELECT user.id AS col1," +
					" user.user_name AS col2," +
					" CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname) AS col3, " +
					" rol.display_name AS col4, " +
					" user.status AS col5 " +
					
					" FROM hre_user AS user" +
					" LEFT OUTER JOIN hre_employee AS emp ON user.emp_number = emp.emp_number" +
					" LEFT OUTER JOIN hre_user_role AS rol ON user.user_role_id = rol.id" +
					
					" WHERE user.deleted = 0 ";
			
		if (form.getUserName() !=null && !form.getUserName().trim().equals("")) {
			sql += " AND user.user_name LIKE '%" + form.getUserName().trim() + "%'";
		}

		if (form.getSelectedUserRole() != null && !form.getSelectedUserRole().trim().equals("") && !form.getSelectedUserRole().trim().equals("0")) {
			sql += " AND rol.id = ?";
			params.add(form.getSelectedUserRole().trim());
		}
		
		if (form.getEmployeeName()!=null && !form.getEmployeeName().trim().equals("")) {
//			sql += " AND CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname) = ?";
//			params.add(form.getEmployeeName().trim());
			
			String tmpName = form.getEmployeeName().trim();
			while(tmpName.contains("  ")){
				tmpName = tmpName.replace("  ", " ");
			}
			if(tmpName.contains("[") && tmpName.contains("]")){
				sql += " AND " +
						"(" +
							"CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname, ' [', emp.main_username, ']') LIKE '%" + tmpName + "%'" + 
							" OR CONCAT(emp.emp_lastname, ' ', emp.emp_firstname, ' [', emp.main_username, ']') LIKE '%" + tmpName + "%'" +
						")";	
			} else {
				sql += " AND " +
						"(" +
							"CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname) LIKE '%" + tmpName + "%'" + 
							" OR CONCAT(emp.emp_lastname, ' ', emp.emp_firstname) LIKE '%" + tmpName + "%'" +
							" OR emp.main_username LIKE '%" + tmpName + "%'" +
						")";	
			}
		}

		if (form.getSelectedStatus() != 2) {
			sql += " AND user.status = ?";
			params.add(form.getSelectedStatus());
		}

		sql += " ORDER BY user.date_created DESC";

		List <Object[]> listObject = baseService.listArrayObject(sql, limit, offset, params.toArray());
		for(int i = 0; listObject != null && i < listObject.size(); i++){

			try {

				Object[] objs = listObject.get(i);
				UserForm user = new UserForm();

				user.setId(Integer.parseInt(CommonUtils.normalizeObjectToString(objs[1], "0")));
				user.setUserName(CommonUtils.normalizeObjectToString(objs[2], ""));
				user.setEmployeeName(CommonUtils.normalizeObjectToString(objs[3], ""));
				user.setUserRole(CommonUtils.normalizeObjectToString(objs[4], ""));
				
				if (objs[5] == null || objs[5].toString() == null) {
					user.setStatus("Disabled");
				} else {
					user.setStatus(objs[5].toString().trim().equals("true") ? "Enabled" : "Disabled");
				}

				listResult.add(user);
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
		
		return listResult;
	}
	
	public int countUsers(UserListForm form){

		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();
		
		sql = "SELECT COUNT(*) AS col1 " +
				
				" FROM hre_user AS user" +
				" LEFT OUTER JOIN hre_employee AS emp ON user.emp_number = emp.emp_number" +
				" LEFT OUTER JOIN hre_user_role AS rol ON user.user_role_id = rol.id" +

				" WHERE user.deleted = 0 ";

		if (form.getUserName() !=null && !form.getUserName().trim().equals("")) {
			sql += " AND user.user_name LIKE '%" + form.getUserName().trim() + "%'";
		}

		if (form.getSelectedUserRole() != null && !form.getSelectedUserRole().trim().equals("") && !form.getSelectedUserRole().trim().equals("0")) {
			sql += " AND rol.id = ?";
			params.add(form.getSelectedUserRole().trim());
		}
		
		if (form.getEmployeeName()!=null && !form.getEmployeeName().trim().equals("")) {
			String tmpName = form.getEmployeeName().trim();
			while(tmpName.contains("  ")){
				tmpName = tmpName.replace("  ", " ");
			}
			if(tmpName.contains("[") && tmpName.contains("]")){
				sql += " AND " +
						"(" +
							"CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname, ' [', emp.main_username, ']') LIKE '%" + tmpName + "%'" + 
							" OR CONCAT(emp.emp_lastname, ' ', emp.emp_firstname, ' [', emp.main_username, ']') LIKE '%" + tmpName + "%'" +
						")";	
			} else {
				sql += " AND " +
						"(" +
							"CONCAT(emp.emp_lastname, ' ', emp.emp_middle_name, ' ', emp.emp_firstname) LIKE '%" + tmpName + "%'" + 
							" OR CONCAT(emp.emp_lastname, ' ', emp.emp_firstname) LIKE '%" + tmpName + "%'" +
							" OR emp.main_username LIKE '%" + tmpName + "%'" +
						")";	
			}
		}

		if (form.getSelectedStatus() != 2) {
			sql += " AND user.status = ?";
			params.add(form.getSelectedStatus());
		}

		try {
			List <Object[]> listObject = baseService.listArrayObject(sql, 0, 0, params.toArray());
			Object o [] = listObject.get(0);
	
			return ((BigInteger) o[1]).intValue();
		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
			return 0;
		}
	}

	public void deleteUsers(UserListForm form){

		ArrayList<UserForm> info = (ArrayList<UserForm>) form.getAllUsers();

		for (int i = 0; i < info.size(); i++) {
			
			try {

				UserForm user = info.get(i);
				if (user.isSelected()) { 
	
					HreUser hreUser = baseService.getFirstPojo("id", Compare.EQUAL, user.getId(), null, null, 0, HreUser.class);
					hreUser.setDeleted(true);
					baseService.saveOrUpdate(hreUser);
				}
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
	}
}
